#include <iostream>
#include <fstream>
#include <sstream>
#include <stdexcept>
#include <bits/stdc++.h>
#include <nlohmann/json.hpp>
#include <sqlite3.h>
#include <regex>
namespace fs = std::filesystem;
using namespace std;

int cnt=0;

bool compare(const vector<string>& a, const vector<string>& b, int n) {
    return a[n] < b[n];
}

void Open_DatabaseFile(const char* filename, sqlite3** database) {
    int rc = sqlite3_open(filename, database);
    if (rc != SQLITE_OK) {
        std::cout << sqlite3_errmsg(*database) << std::endl;
        exit(0);
    }
}
vector<string> findmission(string& lon, string& lat){
    sqlite3 *taskid;  // 卫星可见性数据库 stltaccess.db
    Open_DatabaseFile("/home/dmf/test/database/csvdata.db", &taskid);
    sqlite3_stmt* stmt1;  // 可见性数据库中的数据
    string sqlstr;
    vector<string> id_num;
    double lon_dbl = stod(lon);
    double lat_dbl = stod(lat);
      sqlstr = "SELECT * FROM mission where lon = " + lon + " and lat = " + lat; 
       sqlite3_prepare_v2(taskid, sqlstr.c_str(), -1, &stmt1, NULL);
       while (sqlite3_step(stmt1) == SQLITE_ROW) {
            const unsigned char* id = sqlite3_column_text(stmt1, 0);
            int num = sqlite3_column_int(stmt1, 3);
            if (id) {
                string tid = string(reinterpret_cast<const char*>(id));
                id_num.emplace_back(tid);
                cout<<"22: "<<tid<<endl;
            }
            id_num.emplace_back(to_string(num));
            cout<<"33: "<<num<<endl;
            cout<<"lon: "<<lon<<" ,lat: "<<lat<<endl;
            cnt++;
            cout<<"cnt: "<<cnt<<endl;
       }

    sqlite3_finalize(stmt1);
    sqlite3_close(taskid);
    return id_num;
}
string findscore(string& mission_id){
    sqlite3 *taskmission;  // 卫星可见性数据库 stltaccess.db
    Open_DatabaseFile("/home/dmf/test/database/mission_area.db", &taskmission);
    sqlite3_stmt* stmt2;  // 可见性数据库中的数据
    string sqlstr;
    string onescore;
    sqlstr = "SELECT score FROM mission_area WHERE mission_id = ?";
    sqlite3_prepare_v2(taskmission, sqlstr.c_str(), -1, &stmt2, NULL);

    // 绑定参数
    sqlite3_bind_text(stmt2, 1, mission_id.c_str(), -1, SQLITE_TRANSIENT);

    if (sqlite3_step(stmt2) == SQLITE_ROW) {
        int score = sqlite3_column_int(stmt2, 0);
        onescore = to_string(score);
    } else {
        cout << "No data found for mission_id: " << mission_id << endl;
    }

    sqlite3_finalize(stmt2);
    sqlite3_close(taskmission);
       return onescore;
}
void outcsv(string& tmp, vector<vector<string>>& alltask){
    string outPath = "/home/dmf/test/ttzzbb/file_new/";
    string outName = tmp;
    string outCsvP = outPath + outName + "_area.csv";
    ofstream outcsvFile(outCsvP); // 创建新的CSV文件
    if (!outcsvFile.is_open()) {
        std::cerr << "Unable to open file." << std::endl;
        return;
    }
    outcsvFile << "satallite_id,time,roll_angle,mission_id,mission_lon,mission_lat,sun_elevation_angle,new_score,score,num"<<"\n";
        for(int i=0;i<alltask.size();i++){
        for(int j=0;j<9;j++){
            outcsvFile<<alltask[i][j]<<",";
        }
        outcsvFile<<alltask[i][9]<<endl;
        }
    outcsvFile.close();
}


int main()
{
    sqlite3 *stltaccess;  // 卫星可见性数据库 stltaccess.db
    Open_DatabaseFile("/home/dmf/test/database/stltaccess.db", &stltaccess);
    sqlite3_stmt* stmt;  // 可见性数据库中的数据
    string sqlstr;
    std::string folderPath = "/home/dmf/test/ttzzbb/output_new/";
    double finalscore = 0.0;
    double score_d =0.0;
    double num_d = 0.0;
    int pathLength = folderPath.size();

    // 确保提供的路径是一个目录
    if (!fs::is_directory(folderPath)) {
        std::cerr << "Provided path is not a directory" << std::endl;
        return 1;
    }
    // 使用directory_iterator遍历文件夹中的所有项
    std::vector<std::string> allname;
    for (const auto& entry : fs::directory_iterator(folderPath)) {
        // 检查是否是文件
        if (fs::is_regular_file(entry.status())) {
            allname.emplace_back(entry.path().string().substr(pathLength));
        }
    }
    //for(int i=9;i<10;i++){
    for(int i=0;i<allname.size();i++){
        string tmp = allname[i];
        string id;
        
        vector<vector<string>> alltask;
        for(int j = 0; j < tmp.size(); j++){
            if(tmp[j] != '.'){
                id += tmp[j];
            }
            else break;
        }
        sqlstr = "SELECT fid, time, roll_angle, lon, lat, sun_elevation_angle FROM " + id + ";";
        sqlite3_prepare_v2(stltaccess, sqlstr.c_str(), -1, &stmt, NULL);
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            vector<string> task;
            int colCnt = 0;
            int fid = sqlite3_column_int(stmt, colCnt++);
            int observTime = sqlite3_column_int(stmt, colCnt++);
            double rollangle = sqlite3_column_double(stmt, colCnt++);  
            double lon = sqlite3_column_double(stmt, colCnt++);
            double lat = sqlite3_column_double(stmt, colCnt++);
            double sunAngle = sqlite3_column_double(stmt, colCnt++);
            if(observTime < 0){
                observTime = 0;
            }
            if(id[0]=='o'&&sunAngle<10)continue;
            cout<<"id: "<<id<<endl;
            task.emplace_back(id);
            task.emplace_back(to_string(observTime));
            task.emplace_back(to_string(rollangle));

            vector<string> id_num;
            string tmp1 = to_string(lon);
            string tmp2 = to_string(lat);
            id_num = findmission(tmp1, tmp2);
            if(id_num.size()==0)continue;
            task.emplace_back(id_num[0]);

            task.emplace_back(to_string(lon));
            task.emplace_back(to_string(lat));
            task.emplace_back(to_string(sunAngle));

            
            string score;
            score = findscore(id_num[0]);
            score_d = stod(score);
            num_d = stod(id_num[1]);
            /*
            if(num_d > 15){
                finalscore = score_d * 15 / num_d;
            }
            else{
                finalscore = score_d;
            }
            */
            finalscore = score_d * 2 / num_d;
            task.emplace_back(to_string(finalscore));
            task.emplace_back(score);
            task.emplace_back(id_num[1]);
            //cout<<"score: "<<score<<endl;

            alltask.emplace_back(task);
            std::cout<<"one task finish"<<endl;
            task.clear();
        }
        std::sort(alltask.begin(), alltask.end(), [&](const vector<string>& a, const vector<string>& b) {
        return compare(a, b, 1);
    });

        outcsv(id,alltask);
        alltask.clear();
        std::cout<<"one file finish"<<endl;
    }
    std::cout<<"SUCCESS"<<endl;
    return 0;
}

